package com.huaxia.dao.deptLevels;

import com.huaxia.pojo.deptLevels.DeptAS;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/04 9:07
 */

public interface DeptASMapper {

    @Select("select a.branchsname,a.deptname2,round(NVL(b.issueMoney,0),0)issueMoney2,NVL(a.agentNum,0)agentNum from(\n" +
            "select sb.branchsname,a.deptcode2,a.deptname2,b.agentNum  from SD_BRANCH sb right join(\n" +
            "select t.branchcode,t.deptcode2,t.deptname2 from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('AS','SAS') and t.agentstate='在职')a on sb.branchcode=a.branchcode\n" +
            "left join (\n" +
            "select  t.branchcode,t.deptcode2,t.deptname2, count(*) agentNum  from d_agent_province_yx2 t where instr(deptname2,'虚拟')=0 and instr(deptname2,'总监区')>0 \n" +
            "and t.agentstate='在职' group by t.deptcode2,t.deptname2,t.branchcode) b on a.deptcode2=b.deptcode2 \n" +
            ")a,\n" +
            "(select a.deptcode2,a.monthInSure-b.monthYt issueMoney from(\n" +
            "select t.deptcode2, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day t where instr(t.deptname2,'虚拟')=0 and \n" +
            "TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode2\n" +
            ")a left join (select t.deptcode2,SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day t where instr(t.deptname2,'虚拟')=0 and \n" +
            "TO_CHAR(yt_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode2)b on a.deptcode2=b.deptcode2\n" +
            ")b where a.deptcode2=b.deptcode2 order by issueMoney2 DESC")
    List<DeptAS> getDeptAS();
}
